'''

Case Type： 功能测试
Case Name： substr入参正常对bytea进行截取
Create At:  2020/10/22
Owner    :  opentestcase006
Descption:substr(string, from int [, for int])截取子串。

步骤 1.查看数据库状态，如果数据库没有启动则执行启动，如果已经启动则无操作
步骤 2.清理环境，设置参数
步骤 3.substr的入参是二进制字符串
'''
import os
import unittest
from yat.test import Node
from yat.test import macro
import sys

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH

logger = Logger()

class byte_string_function(unittest.TestCase):

    def setUp(self):
        logger.info("------------------------Opengauss_Function_Binarystring_Substring_Case0001开始执行--------------------------")
        logger.info("-----------查询数据库状态-----------")
        self.commonsh =  CommonSH('dbuser')
        self.common = Common()

    def test_substr(self):
        logger.info("-----------from超范围返回'-----------")
        Normal_SqlMdg1 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'Th\\\\\\000omas'::bytea from 88 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg1)
        self.assertTrue(Normal_SqlMdg1.splitlines()[3].strip() == '')

        logger.info("-----------for 超范围取到末尾-----------")
        Normal_SqlMdg2 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'Th\\\\\\000omas'::bytea from 3 for 89) AS RESULT;""")
        logger.info(Normal_SqlMdg2)
        self.assertTrue(Normal_SqlMdg2.splitlines()[3].strip(' ') == '\\000omas')

        logger.info("-----------from、for是小数四舍五入-----------")
        Normal_SqlMdg3 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'Th\\\\\\000omas'::bytea from 2.3 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg3)
        self.assertTrue(Normal_SqlMdg3.splitlines()[3].strip(' ') == 'h\\000o')
        Normal_SqlMdg31 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'Th\\\\\\000omas'::bytea from 3 for 3.6) AS RESULT;""")
        logger.info(Normal_SqlMdg31)
        self.assertTrue(Normal_SqlMdg31.splitlines()[3].strip(' ') == '\\000oma')

        logger.info("-----------from是负数,for小于from绝对值+1时返回''-----------")
        Normal_SqlMdg4 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'Th\\\\\\000omas'::bytea from -2.3 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg4)
        self.assertTrue(Normal_SqlMdg4.splitlines()[3].strip() == '')
        Normal_SqlMdg41 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'Th\\\\\\000omas'::bytea from -2.6 for 5) AS RESULT;""")
        logger.info(Normal_SqlMdg41)
        self.assertTrue(Normal_SqlMdg41.splitlines()[3].strip(' ') == 'T')

        logger.info("-----------from、for>0-----------")
        Normal_SqlMdg5 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'josejosejose'::bytea from 0 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg5)
        self.assertTrue(Normal_SqlMdg5.splitlines()[3].strip(' ') == 'jo')
        Normal_SqlMdg51 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'josejosejose'::bytea from 1 for 0) AS RESULT;""")
        logger.info(Normal_SqlMdg51)
        self.assertTrue(Normal_SqlMdg51.splitlines()[3].strip() == '')
        Normal_SqlMdg52 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'\\x5402036d6173' from 2 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg52)
        self.assertTrue(Normal_SqlMdg52.splitlines()[3].strip(' ') == '020')
        Normal_SqlMdg53 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(E'jose\\\\\\000\\\\\\000\\\\\\0000jose\\\\\\00000\\\\\\000000jose'::bytea from 2 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg53)
        self.assertTrue(Normal_SqlMdg53.splitlines()[3].strip(' ') == 'ose')
        Normal_SqlMdg54 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(left('ABCDEFGHIJK', 6)::bytea from 2 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg54)
        self.assertTrue(Normal_SqlMdg54.splitlines()[3].strip(' ') == 'BCD')
        Normal_SqlMdg55 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(overlay(E'Th\\\\\\000omas'::bytea placing E'\\\\\\002\\\\\\003'::bytea from 2 for 3) from 2 for 3) AS RESULT;""")
        logger.info(Normal_SqlMdg55)
        self.assertTrue(Normal_SqlMdg55.splitlines()[3].strip(' ') == '\\002\\003m')
        Normal_SqlMdg56 = self.commonsh.execut_db_sql("""set bytea_output to 'escape';
        SELECT substring(lpad('1open', 5000, 'gauss')::bytea from 4998 for 4) AS RESULT;""")
        logger.info(Normal_SqlMdg56)
        self.assertTrue(Normal_SqlMdg56.splitlines()[3].strip(' ') == 'pen')

    def tearDown(self):
        SqlMdg2 = self.commonsh.execut_db_sql('''set bytea_output to 'hex';''')
        logger.info(SqlMdg2)
        logger.info('------------------------Opengauss_Function_Binarystring_Substring_Case0001执行结束--------------------------')